<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/12/2
 * Time: 15:36
 */

namespace App\Modules\SendOrder\Services;

use App\Modules\SendOrder\Models\SendOrder;
use DB;

class SendOrderService
{

    static function searchForDaliy($params, $isAll = false)
    {
        return SendOrder:: searchForDaliy($params, $isAll);
    }

    /**
     * 获取派单下的首充用户数
     * @param $id
     * @return int
     */
    static function getFirstChargeUserNum($id)
    {
        $result = DB::select("SELECT COUNT(*) as num FROM ( SELECT (SELECT IFNULL(COUNT(*),0) as isss FROM orders WHERE uid=a.uid and `status` = 'PAID' and pay_end_at < a.end LIMIT 1)  as temp FROM (SELECT uid,MIN(pay_end_at) as 'end' FROM orders WHERE status='PAID' and send_order_id ='{$id}' GROUP BY uid) a ) b WHERE temp = 0");
        return ($result && isset($result[0]->num)) ? $result[0]->num : 0;
    }

    /**
     * 获取Pv
     * @param $id 派单id
     * @return int PV
     */
    static function getPv($id)
    {
        return SendOrder::getSendOrderTotalPv($id);
    }

    /**
     * 获取Pv
     * @param $id 派单id
     * @return int PV
     */
    static function getUv($id)
    {
        return SendOrder::getSendOrderTotalUv($id);
    }

    /**
     * 获取推广带来的新关粉丝总数
     * @param $send_order_id
     * @return mixed
     */
    static function getNewFansNum($send_order_id)
    {
        return DB::table('force_subscribe_users')
            ->where('send_order_id', $send_order_id)
            ->count();
    }

    /**
     * 获取推广一个月带来的新关粉丝总数
     * @param $month
     * @return mixed
     */
    static function getNewFansNumAtTime($month)
    {
        return DB::table('force_subscribe_users')
            ->where('created_at', '>=', $month . '-01 00:00:00')
            ->where('created_at', '<=', $month . '-31 23:59:59')
            ->count();
    }

    /**
     * 更新派单的备注
     * @param $id
     * @param $distribution_channel_id
     * @param $remark
     * @return mixed
     */
    static function updateRemark($id, $distribution_channel_id, $remark)
    {
        return SendOrder::updateRemark($id, $distribution_channel_id, $remark);
    }

    public static function getSendOrdersStats($distribution_channels, $date)
    {
        return DB::select("SELECT distribution_channels.id,tmp.*,official_accounts.appid,official_accounts.nickname,official_accounts.principal_name FROM distribution_channels
	LEFT JOIN (
			SELECT distribution_channel_id,SUM(cost) as totalCost FROM send_orders WHERE distribution_channel_id
			IN (" . implode(',', $distribution_channels) . ")
			AND DATE(send_time)='{$date}' GROUP BY distribution_channel_id
		) tmp ON tmp.distribution_channel_id=distribution_channels.id
	LEFT JOIN official_accounts ON distribution_channels.id=official_accounts.distribution_channel_id
	WHERE distribution_channels.id IN (" . implode(',', $distribution_channels) . ")");
    }

    public static function getOfficialSendOrderStats($distribution_channels,$date){
        return DB::select("	SELECT distribution_channels.id as distribution_channel_id,tmp.*,DATE(NOW()) AS date,official_accounts.appid,official_accounts.nickname as official_account_name,official_accounts.principal_name as company_name,t2.recharge_amount as charge_amount
FROM distribution_channels
	LEFT JOIN (
			SELECT distribution_channel_id as channel_id,SUM(cost) as cost,agent_name,promotion_point FROM (SELECT send_orders.id AS send_order_id,send_orders.cost,send_orders.distribution_channel_id,agent_info.agent_name,agent_info.agent_intro AS promotion_point FROM send_orders
			 LEFT JOIN agent_info on agent_info.send_order_id=send_orders.id
	WHERE send_orders.distribution_channel_id IN (" .implode(',', $distribution_channels) . ")
		AND DATE(send_time)='{$date}'
		GROUP BY send_orders.id) t GROUP BY t.distribution_channel_id
		) tmp ON tmp.channel_id=distribution_channels.id
	LEFT JOIN (SELECT sum(price) as recharge_amount,distribution_channel_id FROM orders WHERE distribution_channel_id IN (" . implode(',', $distribution_channels) . ") AND `status`='PAID' AND DATE(created_at)='{$date}' group by distribution_channel_id) t2 ON t2.distribution_channel_id=distribution_channels.id
	LEFT JOIN official_accounts ON distribution_channels.id=official_accounts.distribution_channel_id
	WHERE distribution_channels.id IN (" . implode(',', $distribution_channels) . ")
	");
    }

    public static function getOfficialSendOrderStatSum($distribution_channels,$date){
        $res= DB::select("select sum(cost) as cost,(select sum(price) from orders where distribution_channel_id in (" . implode(',', $distribution_channels) . ") and status='PAID' and DATE(created_at)='{$date}') as charge_amount FROM send_orders
	WHERE send_orders.distribution_channel_id IN (" . implode(',', $distribution_channels) . ")
		AND DATE(send_time)='{$date}'
	");
        return $res[0];
    }

}